'Dim oArgs
'Set oArgs = WScript.Arguments

'Dim oNamed

'Set oNamed = oArgs.Named



'Get the computer name argument

Dim dateFolderNameStr

'dateFolderNameStr = oNamed("dateFolderName")


Dim userInputFolderPath
userInputFolderPath=GetUserInputPath()
If userInputFolderPath="" Then
	Dim alertBox
	alertBox = MsgBox ("No Folder Selected!", 0, "Msg")
	WScript.Quit(alertBox)
End If


'Wscript.Echo userInputFolderPath

Set fso = CreateObject("Scripting.FileSystemObject")
Set userInputFolder=fso.GetFolder(userInputFolderPath)

dateFolderNameStr=userInputFolder.Name

'Wscript.Echo dateFolderNameStr

Dim gebizSite
gebizSite="http://gebiz.ncs.com.sg"

Dim arrayNames(0)


Dim curPathStr
curPathStr=userInputFolder.ParentFolder.Path
'curPathStr = fso.GetAbsolutePathName(".")
'Wscript.Echo curPathStr

Dim genFileName
genFileName = curPathStr & "\" & "attachments.xlsx"

Dim typeFolderName
typeFolderName="ITQ"

Set typeFolder=fso.GetFolder(curPathStr & "\" & dateFolderNameStr & "\" & typeFolderName)
Set allSubFiles=typeFolder.Files

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add()
objExcel.Cells(1, 1).Value = "File Name"
objExcel.Cells(1, 1).Interior.ColorIndex = 6

Dim htmlFileNames
Dim htmSurfixNames
htmSurfixNames=Array("htm")
htmlFileNames=getFilesWithSurfixArr(allSubFiles,htmSurfixNames)
'Dim htmlFileNamesLength
'htmlFileNamesLength=getFilesWithSurfixSize(allSubFiles,htmSurfixNames)
'Wscript.Echo htmlFileNamesLength
'ReDim htmlFileNames(htmlFileNamesLength-1)
'getFilesWithSurfix allSubFiles,htmSurfixNames,htmlFileNames


Dim line
line=2

Dim attachFileNameArray
For Each htmlFileName in htmlFileNames
    'Wscript.Echo "htmlFileName:" & htmlFileName
    
    Dim dotPos
    dotPos=InStr(htmlFileName,".")
    'Wscript.Echo dotPos
    Dim fileNamewithoutSurfix
    fileNamewithoutSurfix=Mid(htmlFileName,1,(18-1))
    Dim folderName
    folderName=fileNamewithoutSurfix & "_files"
    'Wscript.Echo folderName

    'Wscript.Echo (typeFolder.Path & folderName)

    Set attachFilesFolder = fso.GetFolder(typeFolder.Path & "\" & folderName)
    Set attachFiles = attachFilesFolder.Files
    Dim gebizUrlPartial
    gebizUrlPartial=gebizSite & "/" & dateFolderNameStr & "/" & "General" & "/"
    
    objExcel.Cells(line, 1).Formula = "=HYPERLINK(" & """" & gebizUrlPartial & htmlFileName & """" & "," & """" & fileNamewithoutSurfix & """" & ")"
    
    
    
    Dim attachSurfixNames
    attachSurfixNames=Array("doc","docx","pdf","xls","xlsx")
	attachFileNameArray=getFilesWithSurfixArr(attachFiles,attachSurfixNames)
    'Dim attachFileNamesLength
    'attachFileNamesLength=getFilesWithSurfixSize(attachFiles,attachSurfixNames)
    'Wscript.Echo attachFileNamesLength
    'ReDim attachFileNameArray(attachFileNamesLength-1)
    'getFilesWithSurfix attachFiles,attachSurfixNames,attachFileNameArray
    Dim count
    count=1 
    For Each attachFileName in attachFileNameArray
        objExcel.Cells(1, count+1).Value = "Attachment " & count
	    objExcel.Cells(1, count+1).Interior.ColorIndex = 6
	    Set seCol=objExcel.Columns(count+1)
	    seCol.ColumnWidth = 15
	    With seCol
	    '.HorizontalAlignment = xlGeneral
	    '.VerticalAlignment = xlBottom
	    .WrapText = True '!!!!!!!!!!!!!!!!!!!!!
	    .Orientation = 0
	    End With
	    Dim s
	    s="""" & gebizUrlPartial & """" & "," & """" & folderName & """" & "," & """" & attachFileName & """"
	    objExcel.Cells(line, count+1).Formula="=HYPERLINK(CONCATENATE(" & s & ")," & """" & attachFileName & """" & ")"
	    count=count+1
    Next
    line=line+1
   
    'objExcel.Cells(1, 1).Value = "Test value"
    'objExcel.Visible = True
Next

objWorkbook.SaveAs(genFileName)
objExcel.Quit

Function getFilesWithSurfixArr(files,surfixNames)
    Dim length
	Dim arr()
    length=0
	ReDim arr(length)
    Dim objFileExtension
    For Each objFile in files
        objFileExtension = fso.GetExtensionName(objFile.Name)
        
        For Each surfixName in surfixNames
            If surfixName=objFileExtension Then
				
				If  length > 0 Then
					Dim tmpArr
					tmpArr=arr
					ReDim arr(length)
					For i=0 to (length-1)
						arr(i)=tmpArr(i)
					Next
				End If
				
				arr(length)=objFile.Name
                length=length+1
                Exit For
            End If
        Next
    Next
	getFilesWithSurfixArr=arr
End Function

Sub getFilesWithSurfix(files,surfixNames,myArray)
    Dim account
    account=0
    Dim objFileExtension
    For Each objFile in files
        
        objFileExtension = fso.GetExtensionName(objFile.Name)
        For Each surfixName in surfixNames
            If surfixName=objFileExtension Then
                myArray(account)=objFile.Name
                'Wscript.Echo account & ": " & objFile.Name
                account=account+1
                Exit For
            End If
        Next
    
    Next
End Sub


Function getFilesWithSurfixSize(files,surfixNames)
    Dim length
    length=0
    Dim objFileExtension
    For Each objFile in files
        objFileExtension = fso.GetExtensionName(objFile.Name)
        'Wscript.Echo objFileExtension
        For Each surfixName in surfixNames
            If surfixName=objFileExtension Then
                length=length+1
                Exit For
            End If
        Next
    Next
    getFilesWithSurfixSize=length
End Function

Function GetUserInputPath()

Dim oDlg, oSourceFolder, oFolderItem

Set oDlg = Wscript.CreateObject("Shell.Application")

Set oSourceFolder = oDlg.BrowseForFolder(&H0, "Select the folder where Gebiz files were downloaded", &H0001)

If InStr(1, TypeName(oSourceFolder), "Folder") > 0 Then
	Set oFolderItem = oSourceFolder.Self
	GetUserInputPath = oFolderItem.Path
Else
	GetUserInputPath = ""
End If
End Function








